package com.example.studytest.Controller.OpenData;


//import com.alibaba.fastjson.JSON;
import com.example.studytest.Entity.OpenData.OpenData;
import com.example.studytest.Entity.Test4.Record_score_info;
import com.example.studytest.Entity.Test4.Test4_info;
import com.example.studytest.Entity.User.T_user;
import com.example.studytest.Repository.OpenData.OpenDataRepository;
import com.example.studytest.Repository.Test1.TestOneRepository;
import com.example.studytest.Repository.Test4.Record_score_infoRepository;
import com.example.studytest.Repository.User.T_userRepository;
import com.example.studytest.Repository.word.vl.VlRepository;
import com.example.studytest.Util.CreateSheetUtil;
import com.example.studytest.Util.ObjectToListUtil;
import jdk.nashorn.internal.parser.JSONParser;

import com.example.studytest.Controller.OpenData.Utils;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

//@CrossOrigin(origins = "https://www.tall-webtest.cn/", maxAge = 3600)
@RestController
@RequestMapping("/openData")
public class OpenDataDownloadController {

    @Autowired
    private OpenDataRepository openDataRepository;

    @Autowired
    private T_userRepository t_userRepository;

    @Autowired
    private Record_score_infoRepository record_score_infoRepository;

    @Autowired
    private VlRepository vlRepository;

    @Autowired
    private TestOneRepository testOneRepository;

    @Autowired
    private T_userRepository tUserRepository;


    @GetMapping("/downloadByEmail")
    public void downloadByEmail(@RequestParam(value = "email") String email, HttpServletResponse response) throws IOException {
        String fileName = "data.csv";
        String sheetName = "testData";
//        String[] heads = {"id","expData","expName","invited_code","session","modality","version","expPhase","trialIndex","itemIndex","stimulus","responseIndex","response","buttonPressed","rt",
//        "timeElapsed","accuracy","score"};

        File csv_data = new File(fileName);


//        List<List<Map<String,Object>>> resultList = new ArrayList<List<Map<String,Object>>>();
        List<Map<String,Object>> resultList = new ArrayList<Map<String,Object>>();
        //查询结果
        List<Map<String,Object>> Avl_Datas = Utils.handleData(openDataRepository.findAVLByEmail(email));
        List<Map<String,Object>> Asd_Datas = Utils.handleData(openDataRepository.findASDByEmail(email));
        List<Map<String,Object>> Alaa_Datas = Utils.handleData(openDataRepository.findALAAByEmail(email));
        List<Map<String,Object>> Awm_Datas = Utils.handleWMData(openDataRepository.findAWMByEmail(email),true);

        List<Map<String,Object>> Wvl_Datas = Utils.handleData(openDataRepository.findWVLByEmail(email));
        List<Map<String,Object>> Wsd_Datas = Utils.handleData(openDataRepository.findWSDByEmail(email));
        List<Map<String,Object>> Wlaa_Datas = Utils.handleData(openDataRepository.findWLAAByEmail(email));
        List<Map<String,Object>> Wwm_Datas = Utils.handleWMData(openDataRepository.findWWMByEmail(email),false);

        resultList.addAll(Avl_Datas);
        resultList.addAll(Asd_Datas);
        resultList.addAll(Alaa_Datas);
        resultList.addAll(Awm_Datas);
        resultList.addAll(Wvl_Datas);
        resultList.addAll(Wsd_Datas);
        resultList.addAll(Wlaa_Datas);
        resultList.addAll(Wwm_Datas);

        System.out.println(resultList.size());


//        ExcelUtils.getInstance().createExcel(resultList,fileName,"测试数据");

        Set<String> keySet = resultList.get(0).keySet();
        String[] heads = keySet.toArray(new String[keySet.size()]);
        csv_data = CreateSheetUtil.createCsv2(fileName, heads, resultList);

        response.setContentType("application/csv; charset=utf-8");
        response.setContentType("application/csv; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
        InputStream in = new FileInputStream(csv_data);
        OutputStream out = response.getOutputStream();
        byte[] bs = {(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};   //new added
        out.write(bs);
        int len;
        byte[] buffer = new byte[1024];
        while ((len = in.read(buffer)) > 0) {
            out.write(buffer, 0, len);
        }

//        resultList.add(Avl_Datas);
//        resultList.add(Asd_Datas);
//        resultList.add(Alaa_Datas);
//        resultList.add(Awm_Datas);


    }

    public List<File> GetCsvlist(List<String> EmailList) throws IOException {
        List<File> CsvList = new ArrayList<>();
        for (int i =0;i < EmailList.size(); i++){
            String email = EmailList.get(i);
            System.out.println(email);
            String fileName = "data_"+email+".csv";
            File csv_data = new File(fileName);

            List<Map<String,Object>> resultList = new ArrayList<Map<String,Object>>();
            //查询结果
            List<Map<String,Object>> Avl_Datas = Utils.handleData(openDataRepository.findAVLByEmail(email));
            List<Map<String,Object>> Asd_Datas = Utils.handleData(openDataRepository.findASDByEmail(email));
            List<Map<String,Object>> Alaa_Datas = Utils.handleData(openDataRepository.findALAAByEmail(email));
            List<Map<String,Object>> Awm_Datas = Utils.handleWMData(openDataRepository.findAWMByEmail(email),true);

            List<Map<String,Object>> Wvl_Datas = Utils.handleData(openDataRepository.findWVLByEmail(email));
            List<Map<String,Object>> Wsd_Datas = Utils.handleData(openDataRepository.findWSDByEmail(email));
            List<Map<String,Object>> Wlaa_Datas = Utils.handleData(openDataRepository.findWLAAByEmail(email));
            List<Map<String,Object>> Wwm_Datas = Utils.handleWMData(openDataRepository.findWWMByEmail(email),false);

            //额外题型，record
            List<Record_score_info> Record_info = record_score_infoRepository.getRSInfoByEmail(email);


            if (Avl_Datas.size()==0 || Asd_Datas.size() == 0 || Alaa_Datas.size()==0 || Awm_Datas.size()==0||
                    Wvl_Datas.size()==0 || Wsd_Datas.size() ==0 || Wlaa_Datas.size() == 0 || Wwm_Datas.size() ==0 || Record_info.size()==0){
                continue;
            }

            resultList.addAll(Avl_Datas);
            resultList.addAll(Asd_Datas);
            resultList.addAll(Alaa_Datas);
            resultList.addAll(Awm_Datas);
            resultList.addAll(Wvl_Datas);
            resultList.addAll(Wsd_Datas);
            resultList.addAll(Wlaa_Datas);
            resultList.addAll(Wwm_Datas);

            Set<String> keySet = resultList.get(0).keySet();
            String[] heads = keySet.toArray(new String[keySet.size()]);
            csv_data = CreateSheetUtil.createCsv2(fileName, heads, resultList);
            CsvList.add(csv_data);

            String fileName2 = "data_"+email+"_RecordInfo.csv";
            String[] heads2 = ObjectToListUtil.getFieldsName(Record_info.get(0));
            File csv_data2 = CreateSheetUtil.createCsv(fileName2, heads2, Record_info);
            CsvList.add(csv_data2);
        }
        return CsvList;
    }

    public List<File> GetPartCsvlist(List<String> EmailList, String part_no) throws IOException {
        List<File> CsvList = new ArrayList<>();
        for (int i =0;i < EmailList.size(); i++){
            String email = EmailList.get(i);
            System.out.println(email);
            String fileName = "data_"+email+".csv";
            File csv_data = new File(fileName);

            List<Map<String,Object>> resultList = new ArrayList<Map<String,Object>>();

            if(part_no=="1"){
                List<Map<String,Object>> Avl_Datas = Utils.handleData(openDataRepository.findAVLByEmail(email));
                List<Map<String,Object>> Wvl_Datas = Utils.handleData(openDataRepository.findWVLByEmail(email));
                if (Avl_Datas.size()==0 || Wvl_Datas.size()==0){
                    continue;
                }
                resultList.addAll(Avl_Datas);
                resultList.addAll(Wvl_Datas);

                Set<String> keySet = resultList.get(0).keySet();
                String[] heads = keySet.toArray(new String[keySet.size()]);
                csv_data = CreateSheetUtil.createCsv2(fileName, heads, resultList);
                CsvList.add(csv_data);
            }
            else if(part_no=="2"){
                List<Map<String,Object>> Asd_Datas = Utils.handleData(openDataRepository.findASDByEmail(email));
                List<Map<String,Object>> Wsd_Datas = Utils.handleData(openDataRepository.findWSDByEmail(email));
                if (Asd_Datas.size()==0 || Wsd_Datas.size()==0){
                    continue;
                }
                resultList.addAll(Asd_Datas);
                resultList.addAll(Wsd_Datas);

                Set<String> keySet = resultList.get(0).keySet();
                String[] heads = keySet.toArray(new String[keySet.size()]);
                csv_data = CreateSheetUtil.createCsv2(fileName, heads, resultList);
                CsvList.add(csv_data);
            }
            else if(part_no=="3"){
                List<Map<String,Object>> Alaa_Datas = Utils.handleData(openDataRepository.findALAAByEmail(email));
                List<Map<String,Object>> Wlaa_Datas = Utils.handleData(openDataRepository.findWLAAByEmail(email));
                if (Alaa_Datas.size()==0 || Wlaa_Datas.size()==0){
                    continue;
                }
                resultList.addAll(Alaa_Datas);
                resultList.addAll(Wlaa_Datas);

                Set<String> keySet = resultList.get(0).keySet();
                String[] heads = keySet.toArray(new String[keySet.size()]);
                csv_data = CreateSheetUtil.createCsv2(fileName, heads, resultList);
                CsvList.add(csv_data);
            }
            else if(part_no=="4"){
                List<Map<String,Object>> Awm_Datas = Utils.handleWMData(openDataRepository.findAWMByEmail(email),true);
                List<Map<String,Object>> Wwm_Datas = Utils.handleWMData(openDataRepository.findWWMByEmail(email),false);
                if (Awm_Datas.size()==0 || Wwm_Datas.size()==0){
                    continue;
                }
                resultList.addAll(Awm_Datas);
                resultList.addAll(Wwm_Datas);

                Set<String> keySet = resultList.get(0).keySet();
                String[] heads = keySet.toArray(new String[keySet.size()]);
                csv_data = CreateSheetUtil.createCsv2(fileName, heads, resultList);
                CsvList.add(csv_data);
            }
            else {
                List<Record_score_info> Record_info = record_score_infoRepository.getRSInfoByEmail(email);
                if (Record_info.size()==0){
                    continue;
                }
                String fileName2 = "data_"+email+"_RecordInfo.csv";
                String[] heads2 = ObjectToListUtil.getFieldsName(Record_info.get(0));
                File csv_data2 = CreateSheetUtil.createCsv(fileName2, heads2, Record_info);
                CsvList.add(csv_data2);
            }
            //查询结果



//            Set<String> keySet = resultList.get(0).keySet();
//            String[] heads = keySet.toArray(new String[keySet.size()]);
//            csv_data = CreateSheetUtil.createCsv2(fileName, heads, resultList);
//            CsvList.add(csv_data);
        }
        return CsvList;
    }

    public List<File> GetMCsvlist(List<String> EmailList, String modality) throws IOException {
        List<File> CsvList = new ArrayList<>();
        for (int i =0;i < EmailList.size(); i++){
            String email = EmailList.get(i);
            System.out.println(email);
            String fileName = "data_"+email+".csv";
            File csv_data = new File(fileName);

            List<Map<String,Object>> resultList = new ArrayList<Map<String,Object>>();

            if(modality=="A" || modality=="a"){
                String expDate = testOneRepository.findExpDate(email);

                List<Map<String,Object>> Avl_Datas = Utils.handleData(openDataRepository.findAVLByEmail(email));
                List<Map<String,Object>> Asd_Datas = Utils.handleData(openDataRepository.findASDByEmail(email));
                List<Map<String,Object>> Alaa_Datas = Utils.handleData(openDataRepository.findALAAByEmail(email));
                List<Map<String,Object>> Awm_Datas = Utils.handleWMData(openDataRepository.findAWMByEmail(email),true);
                List<Record_score_info> Record_info = record_score_infoRepository.getRSInfoByDate(expDate,email);

                if (Avl_Datas.size()==0 || Asd_Datas.size() == 0 || Alaa_Datas.size()==0 || Awm_Datas.size()==0||Record_info.size()==0){
                    continue;
                }
                resultList.addAll(Avl_Datas);
                resultList.addAll(Asd_Datas);
                resultList.addAll(Alaa_Datas);
                resultList.addAll(Awm_Datas);

                String fileName2 = "data_"+email+"_RecordInfo.csv";
                String[] heads2 = ObjectToListUtil.getFieldsName(Record_info.get(0));
                File csv_data2 = CreateSheetUtil.createCsv(fileName2, heads2, Record_info);
                CsvList.add(csv_data2);
            }
            else {
                //查询日期
                String expDate = vlRepository.findExpDate(email);

                List<Map<String,Object>> Wvl_Datas = Utils.handleData(openDataRepository.findWVLByEmail(email));
                List<Map<String,Object>> Wsd_Datas = Utils.handleData(openDataRepository.findWSDByEmail(email));
                List<Map<String,Object>> Wlaa_Datas = Utils.handleData(openDataRepository.findWLAAByEmail(email));
                List<Map<String,Object>> Wwm_Datas = Utils.handleWMData(openDataRepository.findWWMByEmail(email),false);
                List<Record_score_info> Record_info = record_score_infoRepository.getRSInfoByDate(expDate,email);
                if (Wvl_Datas.size()==0 || Wsd_Datas.size() ==0 || Wlaa_Datas.size() == 0 || Wwm_Datas.size() ==0||Record_info.size()==0){
                    continue;
                }

                resultList.addAll(Wvl_Datas);
                resultList.addAll(Wsd_Datas);
                resultList.addAll(Wlaa_Datas);
                resultList.addAll(Wwm_Datas);


                String fileName2 = "data_"+email+"_RecordInfo.csv";
                String[] heads2 = ObjectToListUtil.getFieldsName(Record_info.get(0));
                File csv_data2 = CreateSheetUtil.createCsv(fileName2, heads2, Record_info);
                CsvList.add(csv_data2);
            }

            //查询结果
            Set<String> keySet = resultList.get(0).keySet();
            String[] heads = keySet.toArray(new String[keySet.size()]);
            csv_data = CreateSheetUtil.createCsv2(fileName, heads, resultList);
            CsvList.add(csv_data);


        }
        return CsvList;
    }

    public List<File> GetRCsvlist(List<String> EmailList, String remail) throws IOException {
        List<File> CsvList = new ArrayList<>();

        for (int i =0;i < EmailList.size(); i++){
            String email = EmailList.get(i);
            System.out.println(email);
            String fileName = "data_"+email+".csv";
            File csv_data = new File(fileName);

            List<Map<String,Object>> resultList = new ArrayList<Map<String,Object>>();
            //查询结果
            List<Map<String,Object>> Avl_Datas = Utils.handleData(openDataRepository.findAVLByEmail(email));
            List<Map<String,Object>> Asd_Datas = Utils.handleData(openDataRepository.findASDByEmail(email));
            List<Map<String,Object>> Alaa_Datas = Utils.handleData(openDataRepository.findALAAByEmail(email));
            List<Map<String,Object>> Awm_Datas = Utils.handleWMData(openDataRepository.findAWMByEmail(email),true);

            List<Map<String,Object>> Wvl_Datas = Utils.handleData(openDataRepository.findWVLByEmail(email));
            List<Map<String,Object>> Wsd_Datas = Utils.handleData(openDataRepository.findWSDByEmail(email));
            List<Map<String,Object>> Wlaa_Datas = Utils.handleData(openDataRepository.findWLAAByEmail(email));
            List<Map<String,Object>> Wwm_Datas = Utils.handleWMData(openDataRepository.findWWMByEmail(email),false);

            //额外题型，record
//            List<Record_score_info> Record_info = record_score_infoRepository.getRSInfoByEmail(email);



            if (Avl_Datas.size()==0 || Asd_Datas.size() == 0 || Alaa_Datas.size()==0 || Awm_Datas.size()==0||
                    Wvl_Datas.size()==0 || Wsd_Datas.size() ==0 || Wlaa_Datas.size() == 0 || Wwm_Datas.size() ==0){
                continue;
            }

            resultList.addAll(Avl_Datas);
            resultList.addAll(Asd_Datas);
            resultList.addAll(Alaa_Datas);
            resultList.addAll(Awm_Datas);
            resultList.addAll(Wvl_Datas);
            resultList.addAll(Wsd_Datas);
            resultList.addAll(Wlaa_Datas);
            resultList.addAll(Wwm_Datas);

            Set<String> keySet = resultList.get(0).keySet();
            String[] heads = keySet.toArray(new String[keySet.size()]);
            csv_data = CreateSheetUtil.createCsv2(fileName, heads, resultList);
            CsvList.add(csv_data);


        }
        //User表
        List<T_user> tUsers = tUserRepository.findAllByRemail(remail);
        String fileName2 = "userInfo.csv";
        String[] heads2 = ObjectToListUtil.getFieldsName(tUsers.get(0));
        File csv_data2 = CreateSheetUtil.createCsv(fileName2, heads2, tUsers);
        CsvList.add(csv_data2);

        return CsvList;
    }


    @GetMapping("/downloadAll")
    public void downloadAll(HttpServletResponse response) throws IOException {

        List<String> EmailList = t_userRepository.findAllEmail();
        List<File> CsvList = GetCsvlist(EmailList);
        System.out.println(CsvList.size());
        ZipOutputStream zos = null;
        BufferedInputStream br = null;
//下载方法
        try {
            //文件的名称
            String downloadFilename = "data_all.zip";
            response.reset();
            //设置格式
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(downloadFilename, "UTF-8"));
            //ZipOutputStream来对文件压缩操作
            zos = new ZipOutputStream(response.getOutputStream());
            //循环下载文件，并将之放到ZipOutputStream中
            for (int i = 0; i < CsvList.size(); i++) {
                zos.putNextEntry(new ZipEntry(CsvList.get(i).getName()));
                InputStream in = new FileInputStream(CsvList.get(i));
//                OutputStream out = response.getOutputStream();
                byte[] bs = {(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};   //new added
                zos.write(bs);
                int len;
                byte[] buffer = new byte[1024];
                while ((len = in.read(buffer)) > 0) {
                    zos.write(buffer, 0, len);
                }

                //filePath是下载路径集合
                //fileName是文件名称
            }
            zos.flush();
        } catch (IOException e) {
            System.out.println(e);
        }finally {
            try {
                zos.close();
                br.close();
            } catch (IOException e) {
                System.out.println(e);
            }
        }
    }

    @GetMapping("/downloadByEdu")
    public void downloadByEdu(@RequestParam(value = "edu") String edu, HttpServletResponse response) throws IOException {

        List<String> EmailList = t_userRepository.findEmailByEdu(edu);
        List<File> CsvList = GetCsvlist(EmailList);
        System.out.println(CsvList.size());
        ZipOutputStream zos = null;
        BufferedInputStream br = null;
//下载方法
        try {
            //文件的名称
            String downloadFilename = "data_edu.zip";
            response.reset();
            //设置格式
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(downloadFilename, "UTF-8"));
            //ZipOutputStream来对文件压缩操作
            zos = new ZipOutputStream(response.getOutputStream(), Charset.forName("utf-8"));
            //循环下载文件，并将之放到ZipOutputStream中
            for (int i = 0; i < CsvList.size(); i++) {
                zos.putNextEntry(new ZipEntry(CsvList.get(i).getName()));
                InputStream in = new FileInputStream(CsvList.get(i));
//                OutputStream out = response.getOutputStream();
                byte[] bs = {(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};   //new added
                zos.write(bs);
                int len;
                byte[] buffer = new byte[1024];
                while ((len = in.read(buffer)) > 0) {
                    zos.write(buffer, 0, len);
                }

                //filePath是下载路径集合
                //fileName是文件名称
            }
            zos.flush();
        } catch (IOException e) {
            System.out.println(e);
        }finally {
            try {
                zos.close();
                br.close();
            } catch (IOException e) {
                System.out.println(e);
            }
        }

    }

    @GetMapping("/downloadByMajor")
    public void downloadByMajor(@RequestParam(value = "major") String major,HttpServletResponse response) throws IOException {

        List<String> EmailList = t_userRepository.findEmailByMajor(major);
        List<File> CsvList = GetCsvlist(EmailList);
        System.out.println(CsvList.size());
        ZipOutputStream zos = null;
        BufferedInputStream br = null;
//下载方法
        try {
            //文件的名称
            String downloadFilename = "data_major.zip";
            response.reset();
            //设置格式
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(downloadFilename, "UTF-8"));
            //ZipOutputStream来对文件压缩操作
            zos = new ZipOutputStream(response.getOutputStream());
            //循环下载文件，并将之放到ZipOutputStream中
            for (int i = 0; i < CsvList.size(); i++) {
                zos.putNextEntry(new ZipEntry(CsvList.get(i).getName()));
                InputStream in = new FileInputStream(CsvList.get(i));
//                OutputStream out = response.getOutputStream();
                byte[] bs = {(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};   //new added
                zos.write(bs);
                int len;
                byte[] buffer = new byte[1024];
                while ((len = in.read(buffer)) > 0) {
                    zos.write(buffer, 0, len);
                }

                //filePath是下载路径集合
                //fileName是文件名称
            }
            zos.flush();
        } catch (IOException e) {
            System.out.println(e);
        }finally {
            try {
                zos.close();
                br.close();
            } catch (IOException e) {
                System.out.println(e);
            }
        }
    }

    @GetMapping("/downloadByCetGrade")
    public void downloadByCet4(HttpServletResponse response) throws IOException {
        List<String> EmailList = t_userRepository.findEmailByCetGrade();
        List<File> CsvList = GetCsvlist(EmailList);
        System.out.println(CsvList.size());
        ZipOutputStream zos = null;
        BufferedInputStream br = null;
//下载方法
        try {
            //文件的名称
            String downloadFilename = "data_CetGrade.zip";
            response.reset();
            //设置格式
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(downloadFilename, "UTF-8"));
            //ZipOutputStream来对文件压缩操作
            zos = new ZipOutputStream(response.getOutputStream());
            //循环下载文件，并将之放到ZipOutputStream中
            for (int i = 0; i < CsvList.size(); i++) {
                zos.putNextEntry(new ZipEntry(CsvList.get(i).getName()));
                InputStream in = new FileInputStream(CsvList.get(i));
//                OutputStream out = response.getOutputStream();
                byte[] bs = {(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};   //new added
                zos.write(bs);
                int len;
                byte[] buffer = new byte[1024];
                while ((len = in.read(buffer)) > 0) {
                    zos.write(buffer, 0, len);
                }

                //filePath是下载路径集合
                //fileName是文件名称
            }
            zos.flush();
        } catch (IOException e) {
            System.out.println(e);
        }finally {
            try {
                zos.close();
                br.close();
            } catch (IOException e) {
                System.out.println(e);
            }
        }
    }

    @GetMapping("/downloadByFFlanguage")
    public void downloadByFFLanguage(@RequestParam(value = "FFlanguage") String FFlanguage,HttpServletResponse response) throws IOException {
        List<String> EmailList = t_userRepository.findEmailByFFlanguage(FFlanguage);

        List<File> CsvList = GetCsvlist(EmailList);
        System.out.println(CsvList.size());
        ZipOutputStream zos = null;
        BufferedInputStream br = null;
//下载方法
        try {
            //文件的名称
            String downloadFilename = "data_FFlanguage.zip";
            response.reset();
            //设置格式
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(downloadFilename, "UTF-8"));
            //ZipOutputStream来对文件压缩操作
            zos = new ZipOutputStream(response.getOutputStream());
            //循环下载文件，并将之放到ZipOutputStream中
            for (int i = 0; i < CsvList.size(); i++) {
                zos.putNextEntry(new ZipEntry(CsvList.get(i).getName()));
                InputStream in = new FileInputStream(CsvList.get(i));
//                OutputStream out = response.getOutputStream();
                byte[] bs = {(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};   //new added
                zos.write(bs);
                int len;
                byte[] buffer = new byte[1024];
                while ((len = in.read(buffer)) > 0) {
                    zos.write(buffer, 0, len);
                }

                //filePath是下载路径集合
                //fileName是文件名称
            }
            zos.flush();
        } catch (IOException e) {
            System.out.println(e);
        }finally {
            try {
                zos.close();
                br.close();
            } catch (IOException e) {
                System.out.println(e);
            }
        }
    }

    @GetMapping("/downloadBySex")
    public void downloadBySex(@RequestParam(value = "sex") String sex,HttpServletResponse response) throws IOException {

        List<String> EmailList = t_userRepository.findEmailBySex(sex);
        List<File> CsvList = GetCsvlist(EmailList);
        System.out.println(CsvList.size());
        ZipOutputStream zos = null;
        BufferedInputStream br = null;
//下载方法
        try {
            //文件的名称
            String downloadFilename = "data_sex.zip";
            response.reset();
            //设置格式
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(downloadFilename, "UTF-8"));
            //ZipOutputStream来对文件压缩操作
            zos = new ZipOutputStream(response.getOutputStream());
            //循环下载文件，并将之放到ZipOutputStream中
            for (int i = 0; i < CsvList.size(); i++) {
                zos.putNextEntry(new ZipEntry(CsvList.get(i).getName()));
                InputStream in = new FileInputStream(CsvList.get(i));
//                OutputStream out = response.getOutputStream();
                byte[] bs = {(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};   //new added
                zos.write(bs);
                int len;
                byte[] buffer = new byte[1024];
                while ((len = in.read(buffer)) > 0) {
                    zos.write(buffer, 0, len);
                }

                //filePath是下载路径集合
                //fileName是文件名称
            }
            zos.flush();
        } catch (IOException e) {
            System.out.println(e);
        }finally {
            try {
                zos.close();
                br.close();
            } catch (IOException e) {
                System.out.println(e);
            }
        }
    }

    @GetMapping("/downloadByforeignCountry")
    public void downloadByForeighCountry(HttpServletResponse response) throws IOException {
        List<String> EmailList = t_userRepository.findEmailByForeighCountry();

        List<File> CsvList = GetCsvlist(EmailList);
        System.out.println(CsvList.size());
        ZipOutputStream zos = null;
        BufferedInputStream br = null;
//下载方法
        try {
            //文件的名称
            String downloadFilename = "data.zip";
            response.reset();
            //设置格式
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(downloadFilename, "UTF-8"));
            //ZipOutputStream来对文件压缩操作
            zos = new ZipOutputStream(response.getOutputStream());
            //循环下载文件，并将之放到ZipOutputStream中
            for (int i = 0; i < CsvList.size(); i++) {
                zos.putNextEntry(new ZipEntry(CsvList.get(i).getName()));
                InputStream in = new FileInputStream(CsvList.get(i));
//                OutputStream out = response.getOutputStream();
                byte[] bs = {(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};   //new added
                zos.write(bs);
                int len;
                byte[] buffer = new byte[1024];
                while ((len = in.read(buffer)) > 0) {
                    zos.write(buffer, 0, len);
                }

                //filePath是下载路径集合
                //fileName是文件名称
            }
            zos.flush();
        } catch (IOException e) {
            System.out.println(e);
        }finally {
            try {
                zos.close();
                br.close();
            } catch (IOException e) {
                System.out.println(e);
            }
        }
    }

    @GetMapping("/downloadByExpType")
    public void downloadByExpType(@RequestParam(value = "expType") String expType, HttpServletResponse response) throws IOException {
//        1:Vl,2:Sd,3:la,4:wm
        List<String> EmailList = t_userRepository.findAllEmail();
        List<File> CsvList = GetPartCsvlist(EmailList,expType);
        System.out.println(CsvList.size());
        ZipOutputStream zos = null;
        BufferedInputStream br = null;
//下载方法
        try {
            //文件的名称
            String downloadFilename = "data.zip";
            response.reset();
            //设置格式
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(downloadFilename, "UTF-8"));
            //ZipOutputStream来对文件压缩操作
            zos = new ZipOutputStream(response.getOutputStream());
            //循环下载文件，并将之放到ZipOutputStream中
            for (int i = 0; i < CsvList.size(); i++) {
                zos.putNextEntry(new ZipEntry(CsvList.get(i).getName()));
                InputStream in = new FileInputStream(CsvList.get(i));
//                OutputStream out = response.getOutputStream();
                byte[] bs = {(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};   //new added
                zos.write(bs);
                int len;
                byte[] buffer = new byte[1024];
                while ((len = in.read(buffer)) > 0) {
                    zos.write(buffer, 0, len);
                }

                //filePath是下载路径集合
                //fileName是文件名称
            }
            zos.flush();
        } catch (IOException e) {
            System.out.println(e);
        }finally {
            try {
                zos.close();
                br.close();
            } catch (IOException e) {
                System.out.println(e);
            }
        }

    }

    @GetMapping("/downloadByModality")
    public void downloadByModality(@RequestParam(value = "modality") String modality, HttpServletResponse response) throws IOException {
//        A:aural W:written
        List<String> EmailList = t_userRepository.findAllEmail();
        List<File> CsvList = GetMCsvlist(EmailList, modality);
        System.out.println(CsvList.size());
        ZipOutputStream zos = null;
        BufferedInputStream br = null;
//下载方法
        try {
            //文件的名称
            String downloadFilename = "data.zip";
            response.reset();
            //设置格式
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(downloadFilename, "UTF-8"));
            //ZipOutputStream来对文件压缩操作
            zos = new ZipOutputStream(response.getOutputStream());
            //循环下载文件，并将之放到ZipOutputStream中
            for (int i = 0; i < CsvList.size(); i++) {
                zos.putNextEntry(new ZipEntry(CsvList.get(i).getName()));
                InputStream in = new FileInputStream(CsvList.get(i));
//                OutputStream out = response.getOutputStream();
                byte[] bs = {(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};   //new added
                zos.write(bs);
                int len;
                byte[] buffer = new byte[1024];
                while ((len = in.read(buffer)) > 0) {
                    zos.write(buffer, 0, len);
                }

                //filePath是下载路径集合
                //fileName是文件名称
            }
            zos.flush();
        } catch (IOException e) {
            System.out.println(e);
        }finally {
            try {
                zos.close();
                br.close();
            } catch (IOException e) {
                System.out.println(e);
            }
        }

    }

    //尚未完工
    @GetMapping("/downloadByCountry")
    public void downloadByCountry(@RequestParam(value = "country") String country, HttpServletResponse response) throws IOException {

        List<String> EmailList = t_userRepository.findEmailByCountry(country);
        List<File> CsvList = new ArrayList<>();
        for (int i =0;i < EmailList.size(); i++){
            String email = EmailList.get(i);
            System.out.println(email);
            String fileName = "data_"+email+".csv";
            File csv_data = new File(fileName);

            List<Map<String,Object>> resultList = new ArrayList<Map<String,Object>>();
            //查询结果
            List<Map<String,Object>> Avl_Datas = Utils.handleData(openDataRepository.findAVLByEmail(email));
            List<Map<String,Object>> Asd_Datas = Utils.handleData(openDataRepository.findASDByEmail(email));
            List<Map<String,Object>> Alaa_Datas = Utils.handleData(openDataRepository.findALAAByEmail(email));
            List<Map<String,Object>> Awm_Datas = Utils.handleWMData(openDataRepository.findAWMByEmail(email),true);

            List<Map<String,Object>> Wvl_Datas = Utils.handleData(openDataRepository.findWVLByEmail(email));
            List<Map<String,Object>> Wsd_Datas = Utils.handleData(openDataRepository.findWSDByEmail(email));
            List<Map<String,Object>> Wlaa_Datas = Utils.handleData(openDataRepository.findWLAAByEmail(email));
            List<Map<String,Object>> Wwm_Datas = Utils.handleWMData(openDataRepository.findWWMByEmail(email),false);

            if (Avl_Datas.size()==0 || Asd_Datas.size() == 0 || Alaa_Datas.size()==0 || Awm_Datas.size()==0||
                    Wvl_Datas.size()==0 || Wsd_Datas.size() ==0 || Wlaa_Datas.size() == 0 || Wwm_Datas.size() ==0){
                continue;
            }

            resultList.addAll(Avl_Datas);
            resultList.addAll(Asd_Datas);
            resultList.addAll(Alaa_Datas);
            resultList.addAll(Awm_Datas);
            resultList.addAll(Wvl_Datas);
            resultList.addAll(Wsd_Datas);
            resultList.addAll(Wlaa_Datas);
            resultList.addAll(Wwm_Datas);

            Set<String> keySet = resultList.get(0).keySet();
            String[] heads = keySet.toArray(new String[keySet.size()]);
            csv_data = CreateSheetUtil.createCsv2(fileName, heads, resultList);
            CsvList.add(csv_data);
        }

        System.out.println(CsvList.size());
        ZipOutputStream zos = null;
        BufferedInputStream br = null;
//下载方法
        try {
            //文件的名称
            String downloadFilename = "data.zip";
            response.reset();
            //设置格式
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(downloadFilename, "UTF-8"));
            //ZipOutputStream来对文件压缩操作
            zos = new ZipOutputStream(response.getOutputStream());
            //循环下载文件，并将之放到ZipOutputStream中
            for (int i = 0; i < CsvList.size(); i++) {
                zos.putNextEntry(new ZipEntry(CsvList.get(i).getName()));
                InputStream in = new FileInputStream(CsvList.get(i));
//                OutputStream out = response.getOutputStream();
                byte[] bs = {(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};   //new added
                zos.write(bs);
                int len;
                byte[] buffer = new byte[1024];
                while ((len = in.read(buffer)) > 0) {
                    zos.write(buffer, 0, len);
                }

                //filePath是下载路径集合
                //fileName是文件名称
            }
            zos.flush();
        } catch (IOException e) {
            System.out.println(e);
        }finally {
            try {
                zos.close();
                br.close();
            } catch (IOException e) {
                System.out.println(e);
            }
        }

    }

    @GetMapping("/downloadByRemail")
    public void downloadByRemail(@RequestParam(value = "remail") String remail, HttpServletResponse response) throws IOException {
//        1:Vl,2:Sd,3:la,4:wm

        List<String> EmailList = t_userRepository.findEmailByRemail(remail);
        List<File> CsvList = GetRCsvlist(EmailList, remail);
        System.out.println(CsvList.size());
        ZipOutputStream zos = null;
        BufferedInputStream br = null;
//下载方法
        try {
            //文件的名称
            String downloadFilename = "data.zip";
            response.reset();
            //设置格式
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(downloadFilename, "UTF-8"));
            //ZipOutputStream来对文件压缩操作
            zos = new ZipOutputStream(response.getOutputStream());
            //循环下载文件，并将之放到ZipOutputStream中
            for (int i = 0; i < CsvList.size(); i++) {
                zos.putNextEntry(new ZipEntry(CsvList.get(i).getName()));
                InputStream in = new FileInputStream(CsvList.get(i));
//                OutputStream out = response.getOutputStream();
                byte[] bs = {(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};   //new added
                zos.write(bs);
                int len;
                byte[] buffer = new byte[1024];
                while ((len = in.read(buffer)) > 0) {
                    zos.write(buffer, 0, len);
                }

                //filePath是下载路径集合
                //fileName是文件名称
            }
            zos.flush();
        } catch (IOException e) {
            System.out.println(e);
        }finally {
            try {
                zos.close();
                br.close();
            } catch (IOException e) {
                System.out.println(e);
            }
        }

    }

}
